package com.hnkywl.sts.dao.jggl;

import com.hnkywl.sts.entity.jggl.Jgd;
import com.hnkywl.sts.entity.jggl.Jgdmx;
import com.hnkywl.sts.entity.jggl.Yqccmx;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.*;
import org.hibernate.transform.Transformers;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;

/**
 * Created by Melody on 14-3-12.
 */
@Repository
public class JgdDao extends SimpleHibernateTemplate<Jgd, Long> {

    public Page<Map> findJgdList(String jgdh, Date sqrqStart, Date sqrqEnd, Long sqgsId, Long sbgsId, Integer zt, int pageNo, int pageSize) throws ParseException {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("sqgs", "sqgs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbr", "sbr", CriteriaSpecification.LEFT_JOIN)
                .createAlias("sbgs", "sbgs", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("jgdh as jgdh", new String[]{"jgdh"}, new Type[]{Hibernate.STRING}))
                        .add(Projections.sqlProjection("jgrq as jgrq", new String[]{"jgrq"}, new Type[]{Hibernate.TIMESTAMP}))
                        .add(Projections.sqlProjection("sbrq as sbrq", new String[]{"sbrq"}, new Type[]{Hibernate.TIMESTAMP}))
                        .add(Property.forName("sqgs.qc").as("sqgs_qc"))
                        .add(Property.forName("sqgs.jc").as("sqgs_jc"))
                        .add(Property.forName("sbgs.jc").as("sbgs_jc"))
                        .add(Property.forName("sbgs.qc").as("sbgs_qc"))
                        .add(Property.forName("sbr.xm").as("sbr_xm"))
                        .add(Property.forName("sumsbzl").as("sumsbzl"))
                        .add(Property.forName("jgzl").as("jgzl"))
                        .add(Property.forName("jghzl").as("jghzl"))
                        .add(Property.forName("yrk").as("yrk"))
                        .add(Property.forName("rate").as("rate"))
                        .add(Property.forName("sjrate").as("sjrate"))
                        .add(Projections.sqlProjection("this_.zt as zt", new String[]{"zt"}, new Type[]{Hibernate.STRING}))
                )
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(jgdh)) {
            criteria.add(Property.forName("jgdh").like(jgdh, MatchMode.ANYWHERE));
        }
        if (sqrqStart != null) {
            criteria.add(Restrictions.ge("dateCreated", sqrqStart));
        }
        if (sqrqEnd != null) {
            criteria.add(Restrictions.le("dateCreated", sqrqEnd));
        }
        if (sqgsId != null) {
            criteria.add(Restrictions.eq("sqgs.id", sqgsId));
        }
        if (sbgsId != null) {
            criteria.add(Restrictions.eq("sbgs.id", sbgsId));
        }
        if (zt != null) {
            criteria.add(Restrictions.eq("zt", zt));
        }

        criteria.addOrder(Property.forName("dateCreated").desc());
        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    public List<Map> findSumJgd(String jgdh, Date sqrqStart, Date sqrqEnd, Long sqgsId, Long sbgsId, Integer zt) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select sum(jgzl) as \"jgzl\",sum(jghzl) as \"jghzl\" from ( ";
        String sqlbase = " select sum(jgdmx1_.jg_sbzl) as jgzl,0 as jghzl  from T_BIZ_JGD t left outer join T_BIZ_JGDMX jgdmx1_ on t.ID = jgdmx1_.JGD_ID"
                + " and jgdmx1_.deleted != 1  and jgdmx1_.zt = 0 where t.deleted != 1";
        String sqlJoin = " union all ";
        String sqlbase1 = " select 0 as jgzl,sum(jgdmx2_.jg_sbzl) as \"jghzl\" from T_BIZ_JGD t left outer join T_BIZ_JGDMX jgdmx2_  on t.ID = jgdmx2_.JGD_ID"
                + " and jgdmx2_.deleted != 1  and jgdmx2_.zt = 1  where t.deleted != 1";

        if (StringUtils.isNotEmpty(jgdh)) {
            condition.add(" t.jgdh like :jgdh ");
            param.put("jgdh", "%" + jgdh + "%");
        }
        if (sqrqStart != null) {
            condition.add(" t.DATE_CREATED>= :sqrqStart ");
            param.put("sqrqStart", sqrqStart);
        }
        if (sqrqEnd != null) {
            condition.add(" t.DATE_CREATED<= :sqrqEnd ");
            param.put("sqrqEnd", sqrqEnd);
        } else {
            condition.add(" t.DATE_CREATED<= :sqrqEnd ");
            param.put("sqrqEnd", new Date());
        }
        if (sqgsId != null) {
            condition.add(" t.sqgs_id=:sqgs ");
            param.put("sqgs", sqgsId);
        }
        if (sbgsId != null) {
            condition.add(" t.sbgs_id=:sbgs ");
            param.put("sbgs", sbgsId);
        }
        if (zt != null) {
            condition.add(" t.zt=:zt ");
            param.put("zt", zt);
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ");
        String sql = sqlColumn + sqlbase + " and " + whereSql + sqlJoin + sqlbase1 + " and " + whereSql + ")";

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> list = sqlQuery
                .setProperties(param)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                .list();
        return list;
    }

    public void deleteJgdmx(Long jgdId) {
        String hql = "update Jgdmx set deleted=1 where jgd.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, jgdId);
        query.executeUpdate();
    }

    public void deleteJghjgdmx(Long jgdId) {
        String hql = "update Jghjgdmx set deleted=1 where jgd.id=?";
        Query query = this.getSession().createQuery(hql);
        query.setLong(0, jgdId);
        query.executeUpdate();
    }

    // 查看，明细信息
    public Page findJgdmxDetailList(String mxzt, Long jgdId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");
        Criteria criteria = getSession().createCriteria(Jgdmx.class)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("jtwz.id").as("jtwz.id"))
                        .add(Property.forName("jtwz.code").as("jtwz.code"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz.oldcode"))
                        .add(Property.forName("jtwz.name").as("jtwz.name"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as \"jtwz.ggxh\"", new String[]{"jtwz.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jtwz.dw").as("jtwz.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck.id").as("ck.id"))
                        .add(Property.forName("ck.mc").as("ck.mc"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("sku").as("sku"))
                        .add(Property.forName("jz").as("jz"))
                        .add(Property.forName("kh.khbm").as("khbm"))
                        .add(Property.forName("sbzl").as("sbzl"))
                        .add(Property.forName("jg_sbsl").as("jg_sbsl"))
                        .add(Property.forName("jg_sbzl").as("jg_sbzl"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("jgd.id", jgdId))
                .add(Restrictions.eq("zt", mxzt))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return this.findByCriteria(page, criteria);
    }

    // 加工调入单冲销明细
    public Page findJgdmxDrCxList(String jgdh, String khbm, Integer sftzd, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");
        Criteria criteria = getSession().createCriteria(Jgdmx.class)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("jgd", "jgd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("jtwz.id").as("jtwz.id"))
                        .add(Property.forName("jtwz.code").as("jtwz.code"))
                        .add(Property.forName("jgd.jgdh").as("jgdh"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz.oldcode"))
                        .add(Property.forName("jtwz.name").as("jtwz.name"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as \"jtwz.ggxh\"", new String[]{"jtwz.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jtwz.dw").as("jtwz.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck.id").as("ck.id"))
                        .add(Property.forName("ck.mc").as("ck.mc"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("sku").as("sku"))
                        .add(Property.forName("jz").as("jz"))
                        .add(Property.forName("kh.khbm").as("khbm"))
                        .add(Projections.sqlProjection("this_.sftzd as sftzd", new String[]{"sftzd"}, new Type[]{Hibernate.BOOLEAN}))
                        .add(Property.forName("sbzl").as("sbzl"))
                        .add(Property.forName("jg_sbsl").as("jg_sbsl"))
                        .add(Property.forName("jg_sbzl").as("jg_sbzl"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("zt", "1"))
                .add(Restrictions.eq("sfcx", true))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (StringUtils.isNotEmpty(jgdh)) {
            criteria.add(Property.forName("jgd.jgdh").like("%" + jgdh.trim() + "%", MatchMode.ANYWHERE));
        }
        if (StringUtils.isNotEmpty(khbm)) {
            criteria.add(Property.forName("kh.khbm").like("%" + khbm.trim() + "%", MatchMode.ANYWHERE));
        }
        if (sftzd != null) {
            if (sftzd == 1) {
                criteria.add(Restrictions.eq("sftzd", true));
            } else {
                criteria.add(Restrictions.eq("sftzd", false));
            }
        }
        return this.findByCriteria(page, criteria);
    }

    // 查看，合计
    public Map findJgdHj(String mxzt, Long jgdId) {
        String sql = "select sum(sbsl) as \"sbsl\",sum(sbzl) as \"sbzl\",sum(jg_sbsl) as \"jg_sbsl\",sum(jg_sbzl) as \"jg_sbzl\" from t_biz_jgdmx where jgd_id = ? and zt = ?";
        return (Map) createSQLQuery(sql, jgdId, mxzt).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    //加工调入冲销明细合计
    public Map findJgdDrCxHj(String jgdh, String khbm, Integer sftzd) {
        String sql = "select sum(jgdmx.sbsl) as \"sbsl\",\n" +
                "       sum(jgdmx.sbzl) as \"sbzl\",\n" +
                "       sum(jgdmx.jg_sbsl) as \"jg_sbsl\",\n" +
                "       sum(jgdmx.jg_sbzl) as \"jg_sbzl\"\n" +
                "  from t_biz_jgdmx jgdmx\n" +
                "  left join t_biz_kh kh\n" +
                "  on jgdmx.kh_id = kh.id\n" +
                "  left join t_biz_jgd jgd\n" +
                "   on jgdmx.jgd_id = jgd.id\n" +
                " where jgdmx.zt = 1\n" +
                "   and jgdmx.sfcx = 1 ";
        String sql1 = "";
        String sql2 = "";
        String sql3 = "";

        if (jgdh != null && StringUtils.isNotEmpty(jgdh)) {
            sql1 = " and jgd.jgdh like " + "\'%" + jgdh.trim() + "%\'";
        }
        if (khbm != null && StringUtils.isNotEmpty(khbm)) {
            sql2 = "  and kh.khbm like " + "\'%" + khbm.trim() + "%\'";
        }
        if (sftzd != null) {
            sql3 = " and jgdmx.sftzd = " + sftzd;
        }
        sql = sql + sql1 + sql2 + sql3;
        return (Map) createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    // 修改，加工单明细
    public List<Map> findJgdmxContentList(Long jgdId) {
        Criteria criteria = getSession().createCriteria(Jgdmx.class)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("ck", "ck", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .createAlias("kh", "kh", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("jtwz.id").as("jtwz.id"))
                        .add(Property.forName("jtwz.code").as("jtwz.code"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz.oldcode"))
                        .add(Property.forName("jtwz.name").as("jtwz.name"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as \"jtwz.ggxh\"", new String[]{"jtwz.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jtwz.dw").as("jtwz.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ck.id").as("ck.id"))
                        .add(Property.forName("ck.mc").as("ck.mc"))
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("this_.jgd_id as jgd_id", new String[]{"jgd_id"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jz").as("jz"))
                        .add(Property.forName("sku").as("sku"))
                        .add(Property.forName("kh.khbm").as("khbm"))
                        .add(Property.forName("sbsl").as("sbsl"))
                        .add(Property.forName("sbzl").as("sbzl"))
                        .add(Property.forName("jg_sbsl").as("jg_sbsl"))
                        .add(Property.forName("jg_sbzl").as("jg_sbzl"))
                        .add(Property.forName("bz").as("bz"))
                ).add(Restrictions.eq("jgd.id", jgdId))
                .add(Restrictions.eq("zt", "0"))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }


    public Page findCkdDetail(Long jgdmx_id, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        String countsql = "select count(1) from t_biz_ckd c where 1 = 1 and " +
                " (c.ckd_tz = 0 or (c.ckd_tz = 1 and c.sftzd = 1)) and c.jgdmx_id=" + jgdmx_id;
        String sql = "select c.warehousebillno as \"ckdh\",c.jgsl as \"jgsl\",c.jgzl as \"jgzl\",j.sku as \"sku\" ," +
                "  decode(ckd_tz,0,'出库单',1,'冲销单',2,'已冲销') as \"zt\"," +
                " (select k.khbm from t_biz_kh k where k.id= j.kh_id) as \"khbm\"," +
                " (select g.code from t_sys_gg g where g.id = j.jtwz_id) as \"code\"," +
                " (select g.name from t_sys_gg g where g.id = j.jtwz_id) as \"name\"," +
                " (select g.cz ||'  '||g.ggxh from t_sys_gg g where g.id = j.jtwz_id) as \"ggxh\"," +
                " (select g.dw from t_sys_gg g where g.id = j.jtwz_id) as \"dw\"," +
                " (select s.z from t_sys_sjzdz s where s.id = j.sjzdz_cd_id) as \"cd\"," +
                " (select c.mc from t_sys_ck c where c.id = j.ck_id) as \"mc\" " +
                " from t_biz_ckd c left join t_biz_jgdmx j on c.jgdmx_id = j.id where 1 = 1 and" +
                " (c.ckd_tz in (0,2) or (c.ckd_tz = 1 and c.sftzd = 1)) and jgdmx_id = " + jgdmx_id +
                " order by c.warehousebillno,c.ckd_tz asc , c.date_created desc ";
        BigDecimal count = (BigDecimal) createSQLQuery(countsql).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(sql)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Map findCkdHj(Long jgdmx_id) {
        String sql = "select jg_sbsl as \"jg_sbsl\",jg_sbzl as \"jg_sbzl\" from t_biz_jgdmx where id = ? ";
        return (Map) createSQLQuery(sql, jgdmx_id).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }


    public Page<Map> findCkdcxList(String ckdh, String khbm, Integer sftzd, String cxdh, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String countsql = "select count(1) from t_biz_ckd c left join t_biz_jgdmx j on c.jgdmx_id = j.id" +
                " left join t_biz_kh k on k.id = j.kh_id " +
                " where 1 = 1 and c.ckd_tz = 1 ";
        String sql = "select c.id as \"id\",c.jgdmx_id as \"jgdmxid\", c.cxdh as \"cxdh\",c.warehousebillno as \"ckdh\",c.jgsl as \"jgsl\"," +
                "c.jgzl as \"jgzl\",j.sku as \"sku\" ,c.sftzd as \"sftzd\", k.khbm as \"khbm\"," +
                " (select g.code from t_sys_gg g where g.id = j.jtwz_id) as \"code\"," +
                " (select g.name from t_sys_gg g where g.id = j.jtwz_id) as \"name\"," +
                " (select g.cz ||'  '||g.ggxh from t_sys_gg g where g.id = j.jtwz_id) as \"ggxh\"," +
                " (select g.dw from t_sys_gg g where g.id = j.jtwz_id) as \"dw\"," +
                " (select s.z from t_sys_sjzdz s where s.id = j.sjzdz_cd_id) as \"cd\"," +
                " (select c.mc from t_sys_ck c where c.id = j.ck_id) as \"mc\" " +
                " from t_biz_ckd c left join t_biz_jgdmx j on c.jgdmx_id = j.id " +
                " left join t_biz_kh k on k.id = j.kh_id " +
                "where 1 = 1 and ckd_tz = 1 ";

        if (StringUtils.isNotEmpty(ckdh)) {
            condition.add(" c.warehousebillno like :ckdh ");
            param.put("ckdh", "%" + ckdh + "%");
        }

        if (StringUtils.isNotEmpty(cxdh)) {
            condition.add(" c.cxdh like :cxdh ");
            param.put("cxdh", "%" + cxdh + "%");
        }

        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" k.khbm like :khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (null != sftzd) {
            condition.add(" c.sftzd =:sftzd ");
            param.put("sftzd", sftzd);
        }

        String ordersql = " order by c.sftzd asc ,c.id desc";

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");
        String resultSql = sql + whereSql + ordersql;
        BigDecimal count = (BigDecimal) createSQLQuery(countsql + whereSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Map findCkdcxHj(String ckdh, String khbm, Integer sftzd, String cxdh) {

        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sumSql = "select sum(jgsl) as \"jgsl\",sum(jgzl) as \"jgzl\" from t_biz_ckd c left join t_biz_jgdmx j on c.jgdmx_id = j.id " +
                " left join t_biz_kh k on k.id = j.kh_id  where 1 = 1 and c.ckd_tz = 1 ";
        if (StringUtils.isNotEmpty(ckdh)) {
            condition.add(" c.warehousebillno like :ckdh ");
            param.put("ckdh", "%" + ckdh + "%");
        }

        if (StringUtils.isNotEmpty(cxdh)) {
            condition.add(" c.cxdh like :cxdh ");
            param.put("cxdh", "%" + cxdh + "%");
        }

        if (StringUtils.isNotEmpty(khbm)) {
            condition.add(" k.khbm like :khbm ");
            param.put("khbm", "%" + khbm + "%");
        }

        if (null != sftzd) {
            condition.add(" c.sftzd =:sftzd ");
            param.put("sftzd", sftzd);
        }

        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = " and " + StringUtils.join(condition, " and ");

        return (Map) createSQLQuery(sumSql + whereSql).setProperties(param).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }

    public List findJgdmxIdByContion(Long ckId, Long jgtzId, String warehouseBillNo) {
        String sql = "select j.id as \"jgdmxId\" from t_biz_jgdmx j, t_biz_ckd  c " +
                " where j.id = c.jgdmx_id and j.ck_id = ? and j.jtwz_id = ? and c.warehousebillno = ? and c.ckd_tz = 0 ";
        List list = createSQLQuery(sql, ckId, jgtzId, warehouseBillNo).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return list;
    }

    public List<Map> findJgglYqccmx(Long jgdId) {
        Criteria criteria = getSession().createCriteria(Yqccmx.class)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("jtwz.id").as("wzggId"))
                        .add(Property.forName("jtwz.code").as("wzggCode"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz.oldcode"))
                        .add(Property.forName("jtwz.name").as("wzggName"))
                        .add(Property.forName("jtwz.cz").as("cz"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as \"wzggGgxh\"", new String[]{"wzggGgxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jtwz.dw").as("dw"))
                        .add(Property.forName("cd.id").as("cdId"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("id").as("id"))
                        .add(Projections.sqlProjection("this_.jgd_id as jgd_id", new String[]{"jgd_id"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("ccsl").as("ccsl"))
                        .add(Property.forName("cczl").as("cczl"))
                ).add(Restrictions.eq("jgd.id", jgdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return criteria.list();
    }

    public Page findJgdYqccmxDetailList(Long jgdId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setAutoCount(true);
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setOrder("desc");
        page.setOrderBy("id");
        Criteria criteria = getSession().createCriteria(Yqccmx.class)
                .createAlias("jtwz", "jtwz", CriteriaSpecification.LEFT_JOIN)
                .createAlias("cd", "cd", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("jtwz.id").as("jtwz.id"))
                        .add(Property.forName("jtwz.code").as("jtwz.code"))
                        .add(Property.forName("jtwz.oldcode").as("jtwz.oldcode"))
                        .add(Property.forName("jtwz.name").as("jtwz.name"))
                        .add(Property.forName("jtwz.cz").as("jtwz.cz"))
                        .add(Projections.sqlProjection("jtwz1_.cz||' '||jtwz1_.ggxh as \"jtwz.ggxh\"", new String[]{"jtwz.ggxh"}, new Type[]{Hibernate.STRING}))
                        .add(Property.forName("jtwz.dw").as("jtwz.dw"))
                        .add(Property.forName("cd.id").as("cd.id"))
                        .add(Property.forName("cd.z").as("cd.z"))
                        .add(Property.forName("ccsl").as("ccsl"))
                        .add(Property.forName("cczl").as("cczl"))
                ).add(Restrictions.eq("jgd.id", jgdId))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        return this.findByCriteria(page, criteria);
    }

    public Map findJgdYqccmxHj(Long jgdId) {
        String sql = "select sum(ccsl) as \"ccsl\",sum(cczl) as \"cczl\" from t_biz_yqccmx where jgd_id = ? ";
        return (Map) createSQLQuery(sql, jgdId).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).uniqueResult();
    }
}
